package de.unibremen.mum.app.whatscool;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHandler extends SQLiteOpenHelper {

	// Database Version
	private static final int DATABASE_VERSION = 1;

	// Database Name
	private static final String DATABASE_NAME = "whatsCool";

	// Rating table name
	private static final String TABLE_RATING = "rating";

	// Contacts Table Columns names
	private static final String KEY_ID = "id";
	private static final String KEY_NAME = "name";
	private static final String KEY_PIC = "pic_url";
	private static final String KEY_COMMENT = "comment";
	private static final String KEY_QUEST1 = "quest1";
	private static final String KEY_QUEST2 = "quest2";
	private static final String KEY_QUEST3 = "quest3";
	private static final String KEY_QUEST4 = "quest4";
	private static final String KEY_QUEST5 = "quest5";
	private static final String KEY_QUEST6 = "quest6";
	private static final String KEY_QUEST7 = "quest7";
	private static final String KEY_RESULT = "result";
	private static final String KEY_DATE = "date";

	public DatabaseHandler(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	// Creating Tables
	@Override
	public void onCreate(SQLiteDatabase db) {

		String CREATE_RATING_TABLE = "CREATE TABLE " + TABLE_RATING + "(" + KEY_ID + " INTEGER PRIMARY KEY, "
				+ KEY_NAME + " TEXT, " + KEY_PIC + " TEXT, " + KEY_COMMENT + " TEXT, " + KEY_QUEST1 + " TEXT, "
				+ KEY_QUEST2 + " TEXT, " + KEY_QUEST3 + " TEXT, " + KEY_QUEST4 + " TEXT, " + KEY_QUEST5 + " TEXT, "
				+ KEY_QUEST6 + " TEXT, " + KEY_QUEST7 + " TEXT, " + KEY_RESULT + " TEXT," + KEY_DATE + " TEXT" + ")";
		db.execSQL(CREATE_RATING_TABLE);
	}

	// Upgrading database
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Drop older table if existed
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_RATING);

		// Create tables again
		onCreate(db);
	}

	/**
	 * Fügt ein Rating zur DB hinzu
	 * @param rating Das Rating das in der DB gespeichert werden soll
	 * @return Die ID des Ratings
	 */
	public long addRating(RatingClass rating) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();

		values.put(KEY_NAME, rating.getName());
		values.put(KEY_PIC, rating.getPic());
		values.put(KEY_COMMENT, rating.getComment());
		values.put(KEY_QUEST1, rating.getQuest1());
		values.put(KEY_QUEST2, rating.getQuest2());
		values.put(KEY_QUEST3, rating.getQuest3());
		values.put(KEY_QUEST4, rating.getQuest4());
		values.put(KEY_QUEST5, rating.getQuest5());
		values.put(KEY_QUEST6, rating.getQuest6());
		values.put(KEY_QUEST7, rating.getQuest7());
		values.put(KEY_RESULT, rating.getResult());
		values.put(KEY_DATE, rating.getDate());

		// Inserting Row
		long rowId = db.insert(TABLE_RATING, null, values);

		db.close(); // Closing database connection

		return rowId;
	}

	/**
	 * Aktualisiert ein Rating in der Datenbank
	 * @param rating Das Rating mit den neuen Daten
	 * @param id Die ID des Ratings aus der DB, dass aktualisiert werden soll
	 */
	public void updateRating(RatingClass rating, long id) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();

		values.put(KEY_NAME, rating.getName());
		values.put(KEY_PIC, rating.getPic());
		values.put(KEY_COMMENT, rating.getComment());
		values.put(KEY_QUEST1, rating.getQuest1());
		values.put(KEY_QUEST2, rating.getQuest2());
		values.put(KEY_QUEST3, rating.getQuest3());
		values.put(KEY_QUEST4, rating.getQuest4());
		values.put(KEY_QUEST5, rating.getQuest5());
		values.put(KEY_QUEST6, rating.getQuest6());
		values.put(KEY_QUEST7, rating.getQuest7());
		values.put(KEY_RESULT, rating.getResult());
		values.put(KEY_DATE, rating.getDate());

		// Inserting Row
		db.update(TABLE_RATING, values, KEY_ID + "=" + id, null);

		db.close(); // Closing database connection

	}

	/**
	 * Liefert ein Rating aus der DB
	 * @param id ID des gewünschten Ratings
	 * @return Rating aus der DB
	 */
	public RatingClass getRating(long id) {
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor cursor = db.query(TABLE_RATING, new String[] { KEY_NAME, KEY_PIC, KEY_COMMENT, KEY_QUEST1, KEY_QUEST2,
				KEY_QUEST3, KEY_QUEST4, KEY_QUEST5, KEY_QUEST6, KEY_QUEST7, KEY_RESULT, KEY_DATE }, KEY_ID + "=?",
				new String[] { String.valueOf(id) }, null, null, null, null);

		RatingClass rating = null;
		if (cursor != null && cursor.getCount() > 0) {
			cursor.moveToFirst();
			rating = new RatingClass(cursor.getString(0), cursor.getString(1), cursor.getString(2),
					cursor.getString(3), cursor.getString(4), cursor.getString(5), cursor.getString(6),
					cursor.getString(7), cursor.getString(8), cursor.getString(9), cursor.getString(10),
					cursor.getString(11));
		}

		// return contact
		db.close();
		return rating;
	}

	/**
	 * Liefert die ID eines Ratings mit einer bestimmten URL zu dem zugehörigen Bild
	 * @param imageUrl Die URL des Bildes, dass zu dem gesuchten Rating gehört
	 * @return Die ID des gesuchten Ratings
	 */
	public long getID(String imageUrl) {
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor cursor = db.query(TABLE_RATING, new String[] { KEY_ID }, KEY_PIC + "=?",
				new String[] { String.valueOf(imageUrl) }, null, null, null, null);
		long id = -1;
		if (cursor != null && cursor.getCount() > 0) {
			cursor.moveToFirst();
			id = Long.parseLong(cursor.getString(0));
		}

		db.close();

		return id;
	}

	/**
	 * Liefert die ID des zuletzt angelegten Ratings
	 * @return ID des zuletzt angelegten Ratings
	 */
	public long getIDlast() {
		String query = "SELECT MAX(id) AS max_id FROM rating";
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(query, null);

		long id = -1;
		if (cursor.moveToFirst()) {
			do {
				id = cursor.getLong(0);
			} while (cursor.moveToNext());
		}
		return id;
	}
	
	/**
	 * Liefert eine Liste aller gespeicherten Ratings
	 * @return Liste aller Ratings
	 */
	public ArrayList<RatingClass> getAllRatings(){
		ArrayList<RatingClass> ratinglist = new ArrayList<RatingClass>();
		SQLiteDatabase db = this.getReadableDatabase();		
		String query = "SELECT * FROM rating";
		Cursor cursor = db.rawQuery(query, null);
		
		if (cursor.moveToFirst()) {
			do {
				String id = cursor.getString(0);
				String name = cursor.getString(1);
				String imageUrl = cursor.getString(2);
				String comment = cursor.getString(3);
				String quest1 = cursor.getString(4);
				String quest2 = cursor.getString(5);
				String quest3 = cursor.getString(6);
				String quest4 = cursor.getString(7);
				String quest5 = cursor.getString(8);
				String quest6 = cursor.getString(9);
				String quest7 = cursor.getString(10);
				String result = cursor.getString(11);
				String date = cursor.getString(12);
				RatingClass rating = new RatingClass(name, imageUrl, comment, quest1, quest2, quest3,
						quest4, quest5, quest6, quest7, result, date);
				rating.setId(Integer.parseInt(id));
				ratinglist.add(rating);
				
			} while (cursor.moveToNext());
		}
		
		return ratinglist;
	}

}
